﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;

namespace kpi.KpiForm_uManager
{
    public partial class KpiFormUserMgr : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            Label_UserName.Text = User.Identity.Name;
            Label_Msg.Text = User.Identity.Name + "，请添加您要对其考评的的下级用户。";
            Label_Msg.Visible = true;

            if (!Page.IsCallback)
            {
                //ShowListBox_IncUserName(User.Identity.Name);
                //ShowListBox_ExtUserName(User.Identity.Name);
                ListBox_ShowIncUserNames();
                ListBox_ShowExtUserNames();
            }

            Label_UserGuide.Text = "<br /><br />";
            Label_UserGuide.Text += "<fieldset>";
            Label_UserGuide.Text += "<legend>说明</legend>";
            Label_UserGuide.Text += "<ol>";
            Label_UserGuide.Text += "<li>请您首先添加下属员工。</li>";
            Label_UserGuide.Text += "<li>您可以对已经添加的下级员工的KPI信息进行考评。</li>";
            Label_UserGuide.Text += "<li>不同的管理者账户可以添加同一个下级，但只有最后一次的操作生效。</li>";
            //Label_UserGuide.Text += "<li>确认之后的KPI信息无法修改。</li>";
            Label_UserGuide.Text += "</ol>";
            Label_UserGuide.Text += "</fieldset>";
            Label_UserGuide.Visible = true;
        }

        protected String GetSqlConnectionString()
        {
            return "Data Source=192.168.0.241;Initial Catalog=kpi_forms_db;Persist Security Info=True;User ID=sa;Password=My.SA";
        }

        protected SqlConnection GetSqlConnection()
        {
            SqlConnection SqlConnection = new SqlConnection(GetSqlConnectionString());
            return SqlConnection;
        }

        protected String GetSqlCommand(String SqlCommandType, String UserName)
        {
            switch (SqlCommandType)
            {
                case "select":
                    return "SELECT DISTINCT [UserName] FROM [kpi_forms_db].[dbo].[u_kpi_table] WHERE [u_kpi_m]='" + UserName + "'";

                case "insert":
                    return "INSERT INTO u_kpi_table (id, UserName, u_kpi, u_kpi_std, u_kpi_comm, u_kpi_rat, u_kpi_fx, u_kpi_rec, uM_kpi_rec, u_kpi_ref) VALUES "
        + " (@id, @UserName, @u_kpi, @u_kpi_std, @u_kpi_comm, @u_kpi_rat, @u_kpi_fx, @u_kpi_rec, @uM_kpi_rec, @u_kpi_ref)";

                case "update":
                    return "UPDATE [u_kpi_table] SET [u_kpi_m]=@u_kpi_m WHERE [UserName]=@UserName";

                default:
                    return "";
            }
        }
        
        protected void ExecuteUpdateQuery(String UserId, String DoUserMgrType)
        {
            SqlConnection SqlConnection = GetSqlConnection();
            SqlConnection.Open();
            SqlCommand cmd = null;

            if (DoUserMgrType.Equals("add"))
            {
                cmd = new SqlCommand("INSERT INTO [kpi_forms_db].[dbo].[u_kpi_userInManager] (UserId, UserName, uManagerId, uManagerName) VALUES (@UserId, @UserName, @uManagerId, @uManagerName)", SqlConnection);
                SqlParameter[] SqlParameter = new SqlParameter[4];
                SqlParameter[0] = new SqlParameter("@UserId", SqlDbType.UniqueIdentifier);
                SqlParameter[1] = new SqlParameter("@UserName", SqlDbType.NVarChar, 50);
                SqlParameter[2] = new SqlParameter("@uManagerId", SqlDbType.UniqueIdentifier);
                SqlParameter[3] = new SqlParameter("@uManagerName", SqlDbType.NVarChar, 50);
                
                SqlParameter[0].Value = new System.Guid(UserId);
                SqlParameter[1].Value = GetUserNameById(UserId);
                SqlParameter[2].Value = new System.Guid(GetUserIdByName(User.Identity.Name));
                SqlParameter[3].Value = User.Identity.Name;

                for (int i = 0; i < SqlParameter.Length; i++)
                {
                    cmd.Parameters.Add(SqlParameter[i]);
                }
            }
            else if (DoUserMgrType.Equals("rem"))
            {                
                cmd = new SqlCommand("DELETE FROM [kpi_forms_db].[dbo].[u_kpi_userInManager] WHERE [UserId] = @UserId AND uManagerId = @uManagerId", SqlConnection);
                SqlParameter[] SqlParameter = new SqlParameter[2];
                SqlParameter[0] = new SqlParameter("@UserId", SqlDbType.UniqueIdentifier);
                SqlParameter[1] = new SqlParameter("@uManagerId", SqlDbType.UniqueIdentifier);  
              
                SqlParameter[0].Value = new System.Guid(UserId);
                SqlParameter[1].Value = new System.Guid(GetUserIdByName(User.Identity.Name));

                for (int i = 0; i < SqlParameter.Length; i++)
                {
                    cmd.Parameters.Add(SqlParameter[i]);
                }
            }

            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();
            SqlConnection.Close();
        }

        protected String GetUserNameById(String UserId)
        {
            String UserName = "";

            SqlConnection SqlConnection = new SqlConnection("Data Source=192.168.0.241;Initial Catalog=kpi_aspnet_db;Persist Security Info=True;User ID=sa;Password=My.SA");
            SqlConnection.Open();

            String selectCommand = "SELECT [UserName] FROM [dbo].[aspnet_Users] WHERE [UserId]='" + UserId + "' AND UserName <> 'root'";
            SqlCommand SelectCommand = new SqlCommand(selectCommand, SqlConnection);
            SqlDataReader UserIdReader = SelectCommand.ExecuteReader();

            while (UserIdReader.Read())
            {
                UserName = UserIdReader["UserName"].ToString();
            }

            SqlConnection.Close();

            return UserName;
        }

        protected String GetUserIdByName(String UserName)
        {
            String UserId = "";

            SqlConnection SqlConnection = new SqlConnection("Data Source=192.168.0.241;Initial Catalog=kpi_aspnet_db;Persist Security Info=True;User ID=sa;Password=My.SA");
            SqlConnection.Open();

            String selectCommand = "SELECT [UserId] FROM [dbo].[aspnet_Users] WHERE [UserName]='" + UserName + "' AND UserName <> 'root'";
            SqlCommand SelectCommand = new SqlCommand(selectCommand, SqlConnection);
            SqlDataReader UserIdReader = SelectCommand.ExecuteReader();

            while (UserIdReader.Read())
            {
                UserId = UserIdReader["UserId"].ToString();
            }

            SqlConnection.Close();

            return UserId;
        }

        protected void doUserAddMgr()
        {
            foreach (ListItem item in this.ListBox_ExtUserName.Items)
            {
                if (item.Selected)
                {
                    ExecuteUpdateQuery(item.Value, "add");
                    //ListBox_UserMgr.Items.Insert(0,item);
                }
            }

            ListBox_IncUserName.Items.Clear();
            //ShowListBox_IncUserName(User.Identity.Name);
            ListBox_ShowIncUserNames();

            ListBox_ExtUserName.Items.Clear();
            //ShowListBox_ExtUserName(User.Identity.Name);
            ListBox_ShowExtUserNames();
        }

        protected void doUserRemMgr()
        {
            foreach (ListItem item in this.ListBox_IncUserName.Items)
            {
                if (item.Selected)
                {
                    ExecuteUpdateQuery(item.Value, "rem");
                }
            }

            ListBox_IncUserName.Items.Clear();
            //ShowListBox_IncUserName(User.Identity.Name);
            ListBox_ShowIncUserNames();

            ListBox_ExtUserName.Items.Clear();
            //ShowListBox_ExtUserName(User.Identity.Name);
            ListBox_ShowExtUserNames();
        }

        protected void Button_UserAddMgr_Click(object sender, EventArgs e)
        {
            //貌似无效......
            if (!Page.IsCallback)
            {
                doUserAddMgr();
            }
        }

        protected void Button_UserRemMgr_Click(object sender, EventArgs e)
        {
            if (!Page.IsCallback)
            {
                doUserRemMgr();
            }            
        }

        protected void ShowListBox_IncUserName(String UserName)
        {
            SqlConnection SqlConnection = new SqlConnection("Data Source=192.168.0.241;Initial Catalog=kpi_aspnet_db;Persist Security Info=True;User ID=sa;Password=My.SA");
            SqlConnection.Open();

            String selectCommand = "SELECT DISTINCT [UserName] FROM [kpi_forms_db].[dbo].[u_kpi_table] WHERE [u_kpi_m]='" + UserName + "' AND UserName <> 'root'";
            SqlCommand SelectCommand = new SqlCommand(selectCommand, SqlConnection);
            SqlDataReader IncUserNameReader = SelectCommand.ExecuteReader();

            while (IncUserNameReader.Read())
            {
                ListBox_IncUserName.Items.Add(new ListItem(IncUserNameReader["UserName"].ToString(), IncUserNameReader["UserName"].ToString()));
            }

            SqlConnection.Close();
        }

        protected void ShowListBox_ExtUserName(String UserName)
        {
            SqlConnection SqlConnection = new SqlConnection("Data Source=192.168.0.241;Initial Catalog=kpi_aspnet_db;Persist Security Info=True;User ID=sa;Password=My.SA");
            SqlConnection.Open();

            String selectCommand = "SELECT DISTINCT [UserName] FROM [kpi_forms_db].[dbo].[u_kpi_table] WHERE [u_kpi_m] IS NULL OR [u_kpi_m]<>'" + UserName + "' AND UserName <> 'root'";
            SqlCommand SelectCommand = new SqlCommand(selectCommand, SqlConnection);
            SqlDataReader ExtUserNameReader = SelectCommand.ExecuteReader();

            while (ExtUserNameReader.Read())
            {
                ListBox_ExtUserName.Items.Add(new ListItem(ExtUserNameReader["UserName"].ToString(), ExtUserNameReader["UserName"].ToString()));
            }

            SqlConnection.Close();
        }


        //在ListBox_ExtUserName中显示未定义上级的账户
        protected void ListBox_ShowExtUserNames()
        {
            SqlConnection SqlConnection = new SqlConnection("Data Source=192.168.0.241;Initial Catalog=kpi_aspnet_db;Persist Security Info=True;User ID=sa;Password=My.SA");
            SqlConnection.Open();

            String selectCommand = "SELECT [UserId], [UserName] FROM [kpi_aspnet_db].[dbo].[aspnet_Users] WHERE UserId NOT IN (SELECT UserId FROM [kpi_forms_db].[dbo].[u_kpi_userInManager]) AND UserName <> 'root' ORDER BY UserName";
            SqlCommand SelectCommand = new SqlCommand(selectCommand, SqlConnection);
            SqlDataReader ExtUserNameReader = SelectCommand.ExecuteReader();

            while (ExtUserNameReader.Read())
            {
                ListBox_ExtUserName.Items.Add(new ListItem(ExtUserNameReader["UserName"].ToString(), ExtUserNameReader["UserId"].ToString()));
            }

            SqlConnection.Close();
        }

        //在ListBox_IncUserName中显示当前用户的下级账户
        protected void ListBox_ShowIncUserNames()
        {
            SqlConnection SqlConnection = new SqlConnection("Data Source=192.168.0.241;Initial Catalog=kpi_aspnet_db;Persist Security Info=True;User ID=sa;Password=My.SA");
            SqlConnection.Open();

            String selectCommand = "SELECT [UserId], [UserName] FROM [kpi_aspnet_db].[dbo].[aspnet_Users] WHERE [UserId] IN (SELECT UserId FROM [kpi_forms_db].[dbo].[u_kpi_userInManager] WHERE uManagerName='" +User.Identity.Name+"') ORDER BY UserName";
            SqlCommand SelectCommand = new SqlCommand(selectCommand, SqlConnection);
            SqlDataReader IncUserNameReader = SelectCommand.ExecuteReader();

            while (IncUserNameReader.Read())
            {
                ListBox_IncUserName.Items.Add(new ListItem(IncUserNameReader["UserName"].ToString(), IncUserNameReader["UserId"].ToString()));
            }

            SqlConnection.Close();
        }
    }
}